package rexsee.core.storage;

import java.io.File;

import rexsee.core.browser.Browser;
import rexsee.core.browser.clazz.JavascriptInterface;
import rexsee.core.utilities.Escape;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.os.Environment;

public class RexseeDatabase implements JavascriptInterface {

	public static final String PUBLIC_SCHEME = "file";
	public static final String PUBLIC_SCHEME2 = "database";
	public static final String DATABASE_DEFAULT = "default.db";
	public static final String TABLE_MASTER = "sqlite_master";

	public static final String INTERFACE_NAME = "Database";
	@Override
	public String getInterfaceName() {
		return mBrowser.application.resources.prefix + INTERFACE_NAME;
	}
	@Override
	public JavascriptInterface getInheritInterface(Browser childBrowser) {
		return this;
	}
	@Override
	public JavascriptInterface getNewInterface(Browser childBrowser) {
		return new RexseeDatabase(childBrowser);
	}

	protected final Context mContext;
	protected final Browser mBrowser;

	public RexseeDatabase(Browser browser) {

		mBrowser = browser;
		mContext = browser.getContext();

	}

	public SQLiteDatabase getDatabase(String dbPath) {
		return getDatabase(mBrowser, dbPath);
	}
	public static SQLiteDatabase getDatabase(Browser browser, String dbPath) {
		if (dbPath == null || dbPath.equals("")) {
			return browser.getContext().openOrCreateDatabase(DATABASE_DEFAULT, Context.MODE_PRIVATE, null);
		} else {
			Uri uri = Uri.parse(dbPath);
			String scheme = uri.getScheme();
			if (scheme.equals(PUBLIC_SCHEME) || scheme.equals(PUBLIC_SCHEME2)) {
				if (!(Environment.MEDIA_MOUNTED.equals(Environment.getExternalStorageState()))) {
					browser.exception("RexseeDatabase", "External storage not found.");
					return null;
				}
				String path = uri.getSchemeSpecificPart();
				if (path == null || path.equals("")) {
					browser.exception("RexseeDatabase", "Public database path could not be null.");
					return null;
				}
				File file = new File(path);
				File parent = file.getParentFile();
				if (parent.exists()) {
					if (!parent.isDirectory()) {
						browser.exception("RexseeDatabase", "Parent path is not a directory.");
						return null;
					}
				} else {
					parent.mkdirs();
				}
				try {
					return SQLiteDatabase.openOrCreateDatabase(file.getAbsolutePath(), null);
				} catch (Exception e) {
					browser.exception("RexseeDatabase", "Create public database error: " + e.getLocalizedMessage());
					return null;
				}
			} else if (scheme.equals(browser.application.resources.prefix)) {
				String path = uri.getSchemeSpecificPart();
				if (path == null || path.equals("")) {
					browser.exception("RexseeDatabase", "Private database path could not be null.");
					return null;
				}
				if (path.indexOf("/") >= 0) {
					browser.exception("RexseeDatabase", "Can't use sub-folder in private database path.");
					return null;
				}
				try {
					return browser.getContext().openOrCreateDatabase(path, Context.MODE_PRIVATE, null);
				} catch (Exception e) {
					browser.exception("RexseeDatabase", "Create private database error " + e.getLocalizedMessage());
					return null;
				}
			} else {
				browser.exception("RexseeDatabase", "Unknown scheme, please use format 'file:///......' or 'database:///......' for public database in external storage or '" + browser.application.resources.prefix + ":......' for private database in device storage.");
				return null;
			}
		}
	}

	//JavaScript Interface

	protected boolean isValidPath(String path) {
		return true;
	}

	// Exec
	public void exec(String sql) {
		exec(sql, "");
	}
	public void exec(String sql, String dbPath) {
		try {
			SQLiteDatabase db = getDatabase(dbPath);
			if (db != null) {
				db.execSQL(sql);
				db.close();
			} else {
				mBrowser.exception(getInterfaceName(), "Null Database!");
			}
		} catch (Exception e) {
			mBrowser.exception(getInterfaceName(), e.getMessage());
		}
	}
	// Query
	public String query(String sql) {
		return query(sql, "");
	}
	public String query(String sql, String dbPath) {
		String rtn = "";
		rtn += "{";
		SQLiteDatabase db = getDatabase(dbPath);
		rtn += "\"database\":\"" + dbPath + "\"";
		rtn += ",\"sql\":\"" + Escape.escape(sql) + "\"";
		if (db != null) {
			try {
				Cursor cursor = db.rawQuery(sql, null);
				rtn += ",\"total\":\"" + cursor.getCount() + "\"";

				boolean columnReady = false;
				String columns = "";
				String rows = "";
				for (int i = 0; i < cursor.getCount(); i++) {
					if (i != 0) rows += ",";
					cursor.moveToPosition(i);
					rows += "[";
					for (int j = 0; j < cursor.getColumnCount(); j++) {
						if (!columnReady) {
							if (j != 0) columns += ",";
							columns += "\"" + cursor.getColumnName(j) + "\"";
						}
						if (j != 0) rows += ",";
						rows += "\"" + cursor.getString(j) + "\"";
					}
					columnReady = true;
					rows += "]";
				}

				rtn += ",\"columns\":[" + columns + "]";
				rtn += ",\"rows\":[" + rows + "]";

				cursor.close();
			} catch (Exception e) {
				mBrowser.exception(getInterfaceName(), e.getMessage());
			}
			db.close();
		}
		rtn += "}";
		return rtn;
	}

	//Other
	public boolean databaseExists(String dbPath) {
		try {
			Uri uri = Uri.parse(dbPath);
			String scheme = uri.getScheme();
			String path = uri.getSchemeSpecificPart();
			if (scheme.equals(PUBLIC_SCHEME)) {
				SQLiteDatabase db = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
				boolean rtn = (db != null) ? true : false;
				if (db != null) db.close();
				return rtn;
			} else if (scheme.equals(mBrowser.application.resources.prefix)) {
				String[] list = mContext.databaseList();
				for (int i = 0; i < list.length; i++) {
					if (list[i].equals(path)) return true;
				}
				return false;
			} else {
				return false;
			}
		} catch (Exception e) {
			return false;
		}
	}
	public boolean tableExists(String tableName) {
		return tableExists(tableName, "");
	}
	public boolean tableExists(String tableName, String dbPath) {
		try {
			SQLiteDatabase db = getDatabase(dbPath);
			if (db == null) return false;
			Cursor cursor = db.rawQuery("SELECT name from " + TABLE_MASTER + " where name='" + tableName + "'", null);
			boolean rtn = (cursor.getCount() == 1) ? true : false;
			cursor.close();
			db.close();
			return rtn;
		} catch (Exception e) {
			mBrowser.exception(getInterfaceName(), e.getMessage());
			return false;
		}
	}
	public String getTables() {
		return getTables("");
	}
	public String getTables(String dbPath) {
		SQLiteDatabase db = getDatabase(dbPath);
		if (db != null) {
			String rtn = "";
			Cursor cursor = db.rawQuery("SELECT name FROM " + TABLE_MASTER + " WHERE type = 'table';", null);
			for (int i = 0; i < cursor.getCount(); i++) {
				cursor.moveToPosition(i);
				rtn += (i == 0) ? "\"" + cursor.getString(0) + "\"" : ",\"" + cursor.getString(0) + "\"";
			}
			cursor.close();
			db.close();
			return "[" + rtn + "]";
		} else {
			return "[]";
		}
	}
	public long getTableLength(String tableName) {
		return getTableLength(tableName, "");
	}
	public long getTableLength(String tableName, String dbPath) {
		try {
			SQLiteDatabase db = getDatabase(dbPath);
			if (db == null) return -1;
			long rtn;
			rtn = DatabaseUtils.queryNumEntries(db, tableName);
			db.close();
			return rtn;
		} catch (Exception e) {
			mBrowser.exception(getInterfaceName(), e.getMessage());
			return -1;
		}
	}
	public String getTableColumns(String tableName) {
		return getTableColumns(tableName, "");
	}
	public String getTableColumns(String tableName, String dbPath) {
		SQLiteDatabase db = getDatabase(dbPath);
		if (db != null) {
			String rtn = "";
			Cursor cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 0;", null);
			for (int i = 0; i < cursor.getColumnCount(); i++) {
				rtn += (i == 0) ? "\"" + cursor.getColumnName(i) + "\"" : ",\"" + cursor.getColumnName(i) + "\"";
			}
			cursor.close();
			db.close();
			return "[" + rtn + "]";
		} else {
			return "[]";
		}
	}

	public void clearAllPrivateDatabases() {
		String[] list = mContext.databaseList();
		for (int i = 0; i < list.length; i++) {
			SQLiteDatabase db = mContext.openOrCreateDatabase(list[i], Context.MODE_PRIVATE, null);
			Cursor cursor = db.rawQuery("SELECT name FROM " + TABLE_MASTER + " WHERE type = 'table';", null);
			for (int j = 0; j < cursor.getCount(); j++) {
				cursor.moveToPosition(j);
				db.execSQL("DELETE FROM " + cursor.getString(0) + ";");
			}
			cursor.close();
			db.close();
		}
	}

	//HTML
	public String queryForHTML(String sql) {
		return queryForHTML(mBrowser, sql, "");
	}
	public String queryForHTML(String sql, String dbPath) {
		return queryForHTML(mBrowser, sql, dbPath);
	}
	public static String queryForHTML(Browser browser, String sql, String dbPath) {
		String html = "";
		html += "<HTML><HEAD><TITLE>Rexsee Database Page</TITLE>";
		html += "<STYLE>";
		html += "</STYLE>";
		html += "</HEAD><BODY style='margin:0px;background-color:black;color:white;'>";
		html += "<table width=100% cellspacing=0 style='color:white;font-size:14px;'>";
		html += "<tr><td style='border-bottom:1px solid #888888; padding:10 5 10 5;'><b>Database:</b> <span style=''>" + dbPath + "</span></td></tr>";
		html += "<tr><td style='border-bottom:1px solid #888888; padding:10 5 10 5;'><b>SQL:</b> <span style=''>" + sql + "</span></td></tr>";
		SQLiteDatabase db = getDatabase(browser, dbPath);
		if (db != null) {
			try {
				Cursor cursor = db.rawQuery(sql, null);
				html += "<tr><td style='border-bottom:1px solid #888888; padding:10 5 10 5;'><b>Total Rows:</b> <span style=''>"
							+ cursor.getCount() + "</span></td></tr>";
				html += "<tr><td style='border-bottom:1px solid #888888; padding:10 5 10 5;'>";
				html += "<table cellpadding=3 style='font-size:14px;color:white;border-collapse:collapse;margin-bottom:10px;' border=1>";
				html += "<tr style='background-color:gray;color:white;'>";
				for (int i = 0; i < cursor.getColumnCount(); i++)
					html += "<td>" + cursor.getColumnName(i) + "</td>";
				html += "</tr>";
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToPosition(i);
					html += "<tr>";
					for (int j = 0; j < cursor.getColumnCount(); j++)
						html += "<td>" + cursor.getString(j) + "</td>";
					html += "</tr>";
				}
				html += "</table></td></tr>";
				cursor.close();
			} catch (Exception e) {
				html += "<tr><td style='border-bottom:1px solid #888888; padding:10 5 10 5;'>Database Error: " + e.getMessage() + "</td></tr>";
			}
			db.close();
		} else {
			html += "<tr><td style='border-bottom:1px solid #888888; padding:10 5 10 5;'>Database not found!</td></tr>";
		}
		html += "</table>";
		html += "</BODY></HTML>";
		return html;
	}

}
